using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading;
using BoYuanCore.Framework;
using FreeSql;
using FreeSql.Aop;
using FreeSql.DataAnnotations;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;

//官方文档 freesql.net

namespace BoYuanCore.DBServices.Base
{
    public static class FreeSqlHelper   
    {
        private static IFreeSql _instance = null;
        private static readonly object _instanceLock = new object();

        //由于freesql采用单例模式，故改配置时候不会热更。需要重启iis等服务器
        private  static string _ConnectionString => Appsettings.app(new string[] { "AppSettings", "DbConnection", "ConnectionString" });

        //freesql官网推荐写法
        static Lazy<IFreeSql> myIFreeSqlLazy = new Lazy<IFreeSql>(() =>
                new FreeSql.FreeSqlBuilder()
                    .UseConnectionString(FreeSql.DataType.SqlServer, _ConnectionString)
                    //.UseGenerateCommandParameterWithLambda(true)//lambda 是否启用参数化
                    //.UseNameConvert(NameConvertType.ToLower)//PostgreSQL 要设置为小写 //oracle 要设置为大写
                    .UseAutoSyncStructure(false) //自动同步实体结构到数据库, 正式环境不推荐用true
                    //.UseAdoConnectionPool(true)//使用数据库连接池
                    //.UseMonitorCommand(cmd => Console.WriteLine($"线程：{cmd.CommandText}\r\n"))//监听数据库sql(不想在这里做处理)
                    .Build() //请务必定义成 Singleton 单例模式
        );

        public static IFreeSql Fsql => myIFreeSqlLazy.Value;

        /// <summary>          
        /// 获取实例(单例模式)        
        /// </summary>          
        /// <returns></returns>
        [Obsolete("此方法只为兼容,请使用FreeSqlHelper.Fsql")] 
        public static IFreeSql GetInstance()
        {
            return Fsql;
        }
        // FreeSqlAop功能在mvc项目里FreeSqlSetup方法来实现，本框架层不想注入log组件 ！！！！

        #region 获取实体 https://github.com/dotnetcore/FreeSql/wiki/%E6%9F%A5%E8%AF%A2

        /// <summary>
        /// 获取一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">主键</param>
        /// <returns></returns>
        public static T GetModel<T>(object id) where T : class
        {
            return Fsql.Select<T>().WhereDynamic(id).First();
        }

        #endregion

        #region 添加 https://github.com/dotnetcore/FreeSql/wiki/%E6%B7%BB%E5%8A%A0
        //全部列 < 指定列(InsertColumns) < 忽略列(IgnoreColumns)

        /// <summary>
        /// 添加实体数据，并返回主键值(主键为long类型雪花id,实体需要设置主键特性),null值也会插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static long InsertModel<T>(T t) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            int tempNum = Fsql.Insert<T>(t).ExecuteAffrows();
            return tempNum == 0 ? 0 : t.ID;//0为插入失败
        }

        /// <summary>
        /// 添加实体数据,并指定插入字段集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="insertColumns"></param>
        /// <returns></returns>
        public static long InsertModel<T>(T t, Expression<Func<T, object>> insertColumns) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            int tempNum = Fsql.Insert<T>(t).InsertColumns(insertColumns).ExecuteAffrows();
            return tempNum == 0 ? 0 : t.ID;//0为插入失败
        }

        /// <summary>
        /// 插入一条，并忽略指定字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="ignoreColumns">要忽略的字段</param>
        /// <returns></returns>
        public static long InsertModel_Ignore<T>(T t, Expression<Func<T, object>> ignoreColumns) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            int tempNum = Fsql.Insert<T>(t).IgnoreColumns(ignoreColumns).ExecuteAffrows();
            return tempNum == 0 ? 0 : t.ID;//0为插入失败
        }

        #endregion

        #region 更新 https://github.com/dotnetcore/FreeSql/wiki/%E4%BF%AE%E6%94%B9
        //> 全部列 < 指定列(Set/SetRaw) < 忽略列(IgnoreColumns) 

        /// <summary>
        /// 根据主键更新实体，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static int UpdateModels<T>(T t) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            return Fsql.Update<T>().SetSource(t).ExecuteAffrows();
        }

        /// <summary>
        /// 根据主键更新实体(要指定更新的列)，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static int UpdateModels<T>(T t, Expression<Func<T, object>> columns) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            return Fsql.Update<T>().SetSource(t).UpdateColumns(columns).ExecuteAffrows();
            /*
             mo=new entity(){FontIcon=1,Icon=2,ID=1};
             FreeSqlHelper.UpdateModels(mo,p=>new {p.FontIcon,p.Icon })
             */
        }

        /// <summary>
        /// 根据主键更新实体(并忽略指定字段)，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="ignoreColumns">要忽略的字段</param>
        /// <returns></returns>
        public static int UpdateModels_Ignore<T>(T t, Expression<Func<T, object>> ignoreColumns) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            return Fsql.Update<T>().SetSource(t).IgnoreColumns(ignoreColumns).ExecuteAffrows();
        }


        /// <summary>
        /// 动态更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columns">要更新的字段</param>
        /// <param name="expressionWhere">条件表达式</param>
        /// <returns></returns>
        public static int Update<T>(Expression<Func<T, T>> columns, Expression<Func<T, bool>> expressionWhere) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            //这个方法也可以实现：Fsql.Update<T>(expressionWhere).Set(columns).ExecuteAffrows();
            return Fsql.Update<T>().Set(columns).Where(expressionWhere).ExecuteAffrows();
            // FreeSqlHelper.Update<SysModule>(p => new SysModule() {Icon = src}, p => p.ID.ToString() == id);
        }

        /*  update子查询。 注意freesql的Update方法只能单表操作。
                UPDATE sysModule
                    SET	
                         isend=0
                WHERE id=(SELECT parentID FROM sysModule AS sm WHERE sm.id=0)
            
            //多表update操作或update子查询，应该如下写法。
            int tempNum = db.Select<Entities.SysModule>()
                .Where(p => db.Select<Entities.SysModule>().Where(m => m.ID == id && p.ID == m.ParentID).Any())
                .ToUpdate()
                .Set(p => new Entities.SysModule() {IsLeaf = false})
                .ExecuteAffrows();
         */

        #endregion

        #region 删除方法 https://github.com/dotnetcore/FreeSql/wiki/%E5%88%A0%E9%99%A4

        /// <summary>
        /// 删除ids集合条件的字符串
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids">ids为字符串 "1,2,3"或"1" 形式</param>
        /// <returns></returns>
        public static bool DeleteByIds<T>( string ids) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            var idList = GetLongListByString(ids);
            return Fsql.Delete<T>(idList).ExecuteAffrows() > 0;
        }

        /// <summary>
        /// 根据条件表达式删除，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">表达式</param>
        /// <returns></returns>
        public static int DeleteModels<T>( Expression<Func<T, bool>> expression) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            //此方法也可以实现 ：Fsql.Delete<T>(expression).ExecuteAffrows();
            return Fsql.Delete<T>().Where(expression).ExecuteAffrows();
        }

        #endregion

        #region BulkCopy

        /// <summary>
        /// 批量导入(针对sqlserver)
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="tableName">表名称</param>
        /// <param name="columDic">字段路由(key为datatable的列名称, value 为对应数据库的列名),注意区分大小写</param>
        /// <param name="batchSize">事务行数节点值</param>
        /// <remarks>
        /// //errorBulkCopy为同步失败返回错误信息
        /// var errorBulkCopy = BulkCopy(dt, nameof(BoYuan.Entity.SysLoginLog),
        ///    new Dictionary<string, string>()
        ///    {
        ///        {"id","ID"}, //注意大小写
        ///        {"浏览器名称","BrowserInfo"},
        ///        {"IP地址" ,"IP"},
        ///        {"系统信息" ,"OSInfo"},
        ///        {"错误密码","PwdShow"},
        ///        {"请求信息","UserAgent"},
        ///        {"UserId","UserId"},
        ///        {"UserName","UserName"},
        ///    });
        /// </remarks>
        public static string BulkCopy(DataTable dt, string tableName, Dictionary<string, string> columDic, int batchSize = 100)
        {
            using (SqlConnection destinationConnection = new SqlConnection(FreeSqlHelper.Fsql.Ado.ConnectionString))
            {
                destinationConnection.Open();
                using (SqlTransaction transaction = destinationConnection.BeginTransaction()) // 开始事务
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default, transaction))
                    {
                        bulkCopy.DestinationTableName = tableName;//表名称
                        bulkCopy.BatchSize = batchSize; //每N条为一个事务节点

                        if (columDic != null && columDic.Count > 0)
                        {
                            foreach (var item in columDic)
                            {
                                bulkCopy.ColumnMappings.Add(item.Key, item.Value);//dt列名，数据库表名
                            }
                        }
                        try
                        {
                            bulkCopy.WriteToServer(dt);
                            transaction.Commit(); //事务提交
                            return string.Empty;
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback(); //事务回滚
                            return ex.Message;
                        }
                    }
                }

            }

        }      

        #endregion

        #region 安全创建表

        /// <summary>
        /// 安全的同步创建表，如果数据库中没有此表则根据实体结构创建表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns>数据库是否含有此表</returns>
        public static bool SyncTableStructure<T>() where T : BoYuan.Entity.SnowflakEntity
        {
            bool haveTable = Fsql.DbFirst.ExistsTable(typeof(T).Name.Split('.').Last());
            if (!haveTable)
            {
                Fsql.CodeFirst.SyncStructure<T>();
            }
            return haveTable;
        }


        /// <summary>
        /// 反射实体类,并安全同步对应所有表
        /// </summary>
        /// <remarks>
        /// 调用示例 FreeSqlHelper.SyncStructureByType(typeof(BoYuan.Entity.SnowflakEntity));
        /// </remarks>>
        /// <param name="myType">给定的类型</param>
        /// <returns>所有子类类型</returns>
        public static void SyncAllStructureByType(Type myType)
        {
            var assembly = myType.Assembly;//获取当前父类所在的程序集
            var assemblyAllTypes = assembly.GetTypes();//获取该程序集中的所有类型
            foreach (var itemType in assemblyAllTypes)//遍历所有类型进行查找
            {
                var baseType = itemType.BaseType;//获取元素类型的基类
                if (baseType != null)//如果有基类
                {
                    if (baseType.Name == myType.Name)//如果基类就是给定的父类
                    {
                        bool haveTable = Fsql.DbFirst.ExistsTable(itemType.Name);
                        if (!haveTable)
                        {
                            Fsql.CodeFirst.SyncStructure(itemType);
                        }
                    }
                }
            }
        }
        #endregion

        #region ids转集合

        /// <summary>
        /// 将字符串转成int数组(, 号分割)
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static int[] GetIntListByString(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return null;
            return Array.ConvertAll<string, int>(ids.Split(','), Int32.Parse);
        }

        /// <summary>
        /// 将字符串转成long数组(, 号分割)
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static long[] GetLongListByString(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return null;
            return Array.ConvertAll<string, long>(ids.Split(','),Convert.ToInt64);
        }

        #endregion
    }

    [ExpressionCall]
    public static class MyFreeSqlExpressionCall
    {
        //自定义函数 http://1024todo.cn:8888/%e8%a1%a8%e8%be%be%e5%bc%8f%e5%87%bd%e6%95%b0.html#%E8%87%AA%E5%AE%9A%E4%B9%89%E8%A7%A3%E6%9E%90

        //https://github.com/dotnetcore/FreeSql/issues/243 In多列查询，表达式自定义实现 

        public static ThreadLocal<ExpressionCallContext> expContext = new ThreadLocal<ExpressionCallContext>();
        /// <summary>
        /// C#：从元组集合中查找 exp1, exp2 是否存在<para></para>
        /// SQL： <para></para>
        /// exp1 = that[0].Item1 and exp2 = that[0].Item2 OR <para></para>
        /// exp1 = that[1].Item1 and exp2 = that[1].Item2 OR <para></para>
        /// ... <para></para>
        /// 注意：当 that 为 null 或 empty 时，返回 1=0 <para></para>
        /// Oracle： (Id, Name) IN ((1, "name1"), (2, "name2"))
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <param name="that"></param>
        /// <param name="exp1"></param>
        /// <param name="exp2"></param>
        /// <remarks>
        /// FreeSql 基础库为了不依赖 System.ValueType.dll，所以将以下代码抽离了出来。
        ///        //元组集合
        ///        vae lst = new List<(Guid, DateTime)>();
        ///        lst.Add((Guid.NewGuid(), DateTime.Now));
        ///lst.Add((Guid.NewGuid(), DateTime.Now));
        ///
        ///var t2 = fsql.Select<T>()
        ///  .Where(a => lst.Contains(a.Id, a.ct1))
        ///  .ToList();
        ///        Oracle 产生如下SQL：
        ///
        ///SELECT..FROM..
        ///WHERE(a."Id", a."ct1") in (
        ///('685ee1f6-bdf6-4719-a291-c709b8a1378f','2019-12-07 23:55:27'), 
        ///('5ecd838a-06a0-4c81-be43-1e77633b7404', '2019-12-07 23:55:27'))
        ///非 ORACLE 产生如下 SQL：
        ///
        ///SELECT..FROM..
        ///WHERE(a."Id" = '685ee1f6-bdf6-4719-a291-c709b8a1378f' AND a."ct1" = '2019-12-07 23:55:27' OR
        ///a."Id" = '5ecd838a-06a0-4c81-be43-1e77633b7404' AND a."ct1" = '2019-12-07 23:55:27')
        /// </remarks>
        /// <returns></returns>
        public static bool Contains<T1, T2>([RawValue] this IEnumerable<(T1, T2)> that, T1 exp1, T2 exp2)
        {
            if (expContext.IsValueCreated == false || expContext.Value == null || expContext.Value.ParsedContent == null)
                return that?.Any(a => a.Item1.Equals(exp1) && a.Item2.Equals(exp2)) == true;
            if (that?.Any() != true)
            {
                expContext.Value.Result = "1=0";
                return false;
            }
            var sb = new StringBuilder();
            var idx = 0;

            switch (expContext.Value.DataType)
            {
                case FreeSql.DataType.Oracle:
                case FreeSql.DataType.OdbcOracle:
                    sb.Append("(")
                        .Append(expContext.Value.ParsedContent["exp1"]).Append(", ")
                        .Append(expContext.Value.ParsedContent["exp2"])
                        .Append(") IN (");
                    foreach (var item in that)
                    {
                        if (idx++ > 0) sb.Append(", ");
                        sb.Append("(")
                            .Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T1), item.Item1))).Append(", ")
                            .Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T2), item.Item2)))
                            .Append(")");
                    }
                    sb.Append(")");

                    expContext.Value.Result = sb.ToString();
                    return false;
            }
            foreach (var item in that)
            {
                if (idx++ > 0) sb.Append(" OR \r\n");
                sb
                    .Append(expContext.Value.ParsedContent["exp1"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T1), item.Item1)))
                    .Append(" AND ")
                    .Append(expContext.Value.ParsedContent["exp2"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T2), item.Item2)));
            }
            expContext.Value.Result = sb.ToString();
            return true;
        }
        /// <summary>
        /// C#：从元组集合中查找 exp1, exp2, exp3 是否存在<para></para>
        /// SQL： <para></para>
        /// exp1 = that[0].Item1 and exp2 = that[0].Item2 and exp3 = that[0].Item3 OR <para></para>
        /// exp1 = that[1].Item1 and exp2 = that[1].Item2 and exp3 = that[1].Item3 OR <para></para>
        /// ... <para></para>
        /// 注意：当 that 为 null 或 empty 时，返回 1=0
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <typeparam name="T3"></typeparam>
        /// <param name="that"></param>
        /// <param name="exp1"></param>
        /// <param name="exp2"></param>
        /// <param name="exp3"></param>
        /// <remarks>
        /// FreeSql 基础库为了不依赖 System.ValueType.dll，所以将以下代码抽离了出来。
        /// //元组集合
        /// vae lst = new List<(Guid, DateTime, string)>();
        /// lst.Add((Guid.NewGuid(), DateTime.Now,"张三"));
        /// lst.Add((Guid.NewGuid(), DateTime.Now,"李四"));
        ///
        /// var t2 = fsql.Select<T>()
        /// .Where(a => lst.Contains(a.Id, a.ct1, a.Name))
        /// .ToList();
        ///        Oracle 产生如下SQL：
        ///SELECT..FROM..
        ///WHERE(a."Id", a."ct1",a."Name") in (
        ///('685ee1f6-bdf6-4719-a291-c709b8a1378f','2019-12-07 23:55:27','张三'), 
        ///('5ecd838a-06a0-4c81-be43-1e77633b7404','2019-12-07 23:55:27','李四'))
        ///非 ORACLE 产生如下 SQL：
        ///
        ///SELECT..FROM..
        ///WHERE(a."Id" = '685ee1f6-bdf6-4719-a291-c709b8a1378f' AND a."ct1" = '2019-12-07 23:55:27' AND a."Name" = '张三' OR
        ///a."Id" = '5ecd838a-06a0-4c81-be43-1e77633b7404' AND a."ct1" = '2019-12-07 23:55:27' AND a."Name" = '李四')
        /// </remarks>
        /// <returns></returns>
        public static bool Contains<T1, T2, T3>([RawValue] this IEnumerable<(T1, T2, T3)> that, T1 exp1, T2 exp2, T3 exp3)
        {
            if (expContext.IsValueCreated == false || expContext.Value == null || expContext.Value.ParsedContent == null)
                return that?.Any(a => a.Item1.Equals(exp1) && a.Item2.Equals(exp2) && a.Item3.Equals(exp3)) == true;
            if (that?.Any() != true)
            {
                expContext.Value.Result = "1=0";
                return false;
            }
            var sb = new StringBuilder();
            var idx = 0;

            switch (expContext.Value.DataType)
            {
                case FreeSql.DataType.Oracle:
                case FreeSql.DataType.OdbcOracle:
                    sb.Append("(")
                        .Append(expContext.Value.ParsedContent["exp1"]).Append(", ")
                        .Append(expContext.Value.ParsedContent["exp2"]).Append(", ")
                        .Append(expContext.Value.ParsedContent["exp3"])
                        .Append(") IN (");
                    foreach (var item in that)
                    {
                        if (idx++ > 0) sb.Append(", ");
                        sb.Append("(")
                            .Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T1), item.Item1))).Append(", ")
                            .Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T2), item.Item2))).Append(", ")
                            .Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T3), item.Item3)))
                            .Append(")");
                    }
                    sb.Append(")");

                    expContext.Value.Result = sb.ToString();
                    return false;
            }
            foreach (var item in that)
            {
                if (idx++ > 0) sb.Append(" OR \r\n");
                sb.Append(expContext.Value.ParsedContent["exp1"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T1), item.Item1)))
                  .Append(" AND ")
                  .Append(expContext.Value.ParsedContent["exp2"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T2), item.Item2)))
                  .Append(" AND ")
                  .Append(expContext.Value.ParsedContent["exp3"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T3), item.Item3)));
            }
            expContext.Value.Result = sb.ToString();
            return true;
        }

        /// <summary>
        /// 根据字典，拼接case when
        /// </summary>
        /// <typeparam name="TInput"></typeparam>
        /// <typeparam name="TOutput"></typeparam>
        /// <param name="input"></param>
        /// <param name="dict"></param>
        /// <remarks>
        /// .ToSql( p=> new{ haha= MyFreeSqlExpressionCall.CaseDict(p.ParentID, dic)} ); 
        /// </remarks>
        /// <returns></returns>
        public static TOutput CaseDict<TInput, TOutput>(TInput input, [RawValue] Dictionary<TInput, TOutput> dict)
        {
            var ec = expContext.Value;
            var sb = new StringBuilder();
            sb.Append(" case");
            foreach (var kv in dict)
                sb.Append(" when ").Append(ec.ParsedContent["input"]).Append(" = ").Append(ec.FormatSql(kv.Key))
                    .Append(" then ").Append(ec.FormatSql(kv.Value));
            sb.Append(" end");
            ec.Result = sb.ToString();
            return default(TOutput);
        }

        /// <summary>
        /// 根据字典，拼接case when
        /// </summary>
        /// <typeparam name="TInput"></typeparam>
        /// <typeparam name="TOutput"></typeparam>
        /// <param name="input"></param>
        /// <param name="dict"></param>
        /// <param name="elseOutput">else 值</param>
        /// <remarks>
        /// .ToSql( p=> new{ haha= MyFreeSqlExpressionCall.CaseDict(p.ParentID, dic, "一")} ); 
        /// </remarks>
        /// <returns></returns>
        public static TOutput CaseDict<TInput, TOutput>(TInput input, [RawValue] Dictionary<TInput, TOutput> dict, TOutput elseOutput)
        {
            var ec = expContext.Value;
            var sb = new StringBuilder();
            sb.Append(" case");
            foreach (var kv in dict)
                sb.Append(" when ").Append(ec.ParsedContent["input"]).Append(" = ").Append(ec.FormatSql(kv.Key))
                    .Append(" then ").Append(ec.FormatSql(kv.Value));

            sb.Append(" else ").Append(ec.FormatSql(elseOutput));

            sb.Append(" end");
            ec.Result = sb.ToString();
            return default(TOutput);
        }

        /*           
        //方法2 ：如果手写case when，可以用下面方法

       .ToList(p => new
                {
                    NewIp = p.IP,

                    title = SqlExt.Case()
                    .When(p.ID == 1, "a")
                    .When(p.ID == 2, "b")
                    .When(p.ID == 3, "c")
                    .When(a.Id == 5, SqlExt.Case().When(b.Id == 1, 10000).Else(999).End())
                    .Else("haha")
                    .End()
                }
            );
         */

        /// <summary>
        /// 单个like实现( name like '张_三')。如果是like %，不推荐用此方法，请用contains startwith endwith
        /// </summary>
        /// <param name="that"></param>
        /// <param name="arg1"></param>
        /// <remarks>
        /// .Where(p=>p.RealName.Like("张_三") && !p.RealName.Contains("李"))
        /// </remarks>
        /// <returns></returns>
        public static bool Like(this string that, string arg1)
        {
            var up = expContext.Value;
            //内容重写
            up.Result = $" {up.ParsedContent["that"]} like {up.ParsedContent["arg1"]} ";
            return true;
        }

        

        /// <summary>
        /// 实现字段内容是 1,2,3,4,5 格式，查询包含指定数字是2
        /// </summary>
        /// <param name="that"></param>
        /// <param name="arg1"></param>
        /// <param name="separator">默认格式是用 逗号分隔</param>
        /// <remarks>
        /// .Where(p=>"1,2,3,4,5".LikeContains(p.ID.ToString()))
        /// 如果用非逗号分隔数据，例如用竖线分隔数据 则
        /// .Where(p=>"1|2|3|4|5".LikeContains(p.ID.ToString(),'|'))
        /// </remarks>
        /// <returns></returns>
        public static bool LikeContains(this string that, string arg1, char separator=',')
        {
            var up = expContext.Value;
            //内容重写
            up.Result = $" '{separator}'+{up.ParsedContent["that"]}+'{separator}' like '%{separator}'+{up.ParsedContent["arg1"]}+ '{separator}%' ";
            return true;
        }
        
        /// <summary>
        /// 实现多个like or like 拼接
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <param name="that"></param>
        /// <param name="exp1"></param>
        /// <remarks>
        /// .Where(p => nameList.ContainsLike(p.Name))
        /// </remarks>
        /// <returns></returns>
        public static bool LikeContains<T1>([RawValue] this IEnumerable<T1> that, T1 exp1)
        {
            if (expContext.IsValueCreated == false || expContext.Value == null || expContext.Value.ParsedContent == null)
                return that?.Any(a => a.Equals(exp1)) == true;
            if (that?.Any() != true)
            {
                expContext.Value.Result = "1=1";
                return false;
            }
            var sb = new StringBuilder();
            var idx = 0;
            foreach (var item in that)
            {
                if (idx++ > 0) sb.Append(" OR \r\n");
                sb.Append(expContext.Value.ParsedContent["exp1"]).Append(" like ").Append(expContext.Value.FormatSql("%"+FreeSql.Internal.Utils.GetDataReaderValue(typeof(T1), item)+"%"));
            }
            expContext.Value.Result = sb.ToString();
            return true;
        }

    }
}


#region 多表连接例子  https://github.com/dotnetcore/FreeSql/wiki/%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2
/*
 //多表连接例子

fsql.Select<Topic, Category, CategoryType>()
  .LeftJoin((a,b,c) => a.CategoryId == b.Id)
  .LeftJoin((a,b,c) => b.ParentID == c.Id)
  .Where((a,b,c) => c.Id > 0)
  .ToList((a,b,c) => new { a,b,c });

//或者
fsql.Select<Topic>().From<Category, CategoryType>((s, b, c) => s
  .LeftJoin(a => a.CategoryId == b.Id)
  .LeftJoin(a => b.ParentID == c.Id))
  .Where((a,b,c) => c.Id > 0)
  .ToList((a,b,c) => new { a,b,c });
//SELECT ...
//FROM `Topic` a
//LEFT JOIN `Category` b ON a.`CategoryId` = b.`Id`
//LEFT JOIN `CategoryType` c ON b.`ParentId` = c.`Id`
//WHERE c. `Id` > 0
 */
#endregion

#region 调用事务的例子
/*
    try
    {
        DB.Transaction(() => {
            //DB.Ado.TransactionCurrentThread 获得当前事务对象。如果有异常 会抛异常 会自动回滚。
    
            //删除
            if (ids.Count > 0)
                DB.Delete<BoYuan.Entity.tb>(ids).ExecuteAffrows();
    
            //批量更新
            if (updateList.Count > 0)
            {
                DB.Update<BoYuan.Entity.tb>().SetSource(updateList).ExecuteAffrows();
            }
    
            //批量添加
            if (addList.Count > 0)
            {
                DB.Insert<BoYuan.Entity.tb>(addList).ExecuteAffrows(); //
            }
        });
    
        saveState = true;               
    }
    catch (Exception ex)
    {
        saveState = false;
    }
 */
#endregion

#region Dto 映射查询
/*
Dto 映射查询

fsql.Select<Song>().ToList<Dto>();
//默认的映射查询，Dto 与 Song 属性名相同的被查询

fsql.Select<Song>().ToList(a => new DTO { xxx = a.ext }) 
//情况1：附加所有映射，再额外映射 ext，返回 List<DTO>

fsql.Select<Song>().ToList(a => new Song { id = a.id }) 
//情况2：只查询 id，返回 List<Song>

fsql.Select<Song>().ToList(a => new { id = a.id }) 
//情况3：只查询 id，返回 List<匿名对象>

fsql.Select<Song>().ToList(a => new DTO(a.id))
//情况4：只查询 id，返回 List<DTO>

fsql.Select<Song>().ToList(a => new DTO(a.id) { xxx = a.ext })
//情况5：查询 id, ext，返回 List<DTO>

fsql.Select<Song>().ToList(a => new Song(a.id))
//情况6：查询 id，返回 List<Song>

fsql.Select<Song>().ToList(a => new Song(a.id) { xxx = a.ext })
//情况7：查询 id, ext，返回 List<Song>
GroupBy 所有方法不使用 DTO 映射规则
这种映射支持单表/多表，在查询数据之前映射（不是先查询所有字段再到内存映射）

查找规则，查找属性名，会循环内部对象 _tables（join 查询后会增长），以 主表优先查，直到查到相同的字段。

如：

A, B, C 都有 id，Dto { id, a1, a2, b1, b2 }，A.id 被映射。也可以指定 id = C.id 映射。


总结  默认都会按照字段名称匹配来映射，  就是一些特殊的字段，需要你自己来设定(附加映射)。

自动映射，可能导致一些映射不是想要的
比如Dto Name 在多表查询得时候，多个表都有 Name 字段，默认会映射到第一个表的 Name 查询
此时，如果像将它改成第二个表 Name，ToList((a,b) => new Dto {Name=b.Name})  
Dto 其他字段不指定，会按字段名称自动映射

还有一种情况，Dto 有一两个属性名，和字段不一样的时候，也可以这样附加映射
ToList(a=> new Dto {NameXxx=a.Name})
 */

#endregion